﻿/**********************************天使物流*******************************************
* 功能描述:基本信息-车辆
* 作者:李周蓬
* 时间:
* 
**********************************天使物流*******************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TianShiProject.EntityFrameworkCore;
using TianShiProject.Models.BaseInfo.Cars;
using TianShiProject.Models.Common;
using Volo.Abp.EntityFrameworkCore;

namespace TianShiProject.BaseInfo.Cars
{
    public class CarRepository : DapperDbContextRepository, ICarRepository
    {
        public CarRepository(IDbContextProvider<TianShiProjectDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }
        /// <summary>
        /// 显示查询分页
        /// </summary>
        /// <param name="carBrand"></param>
        /// <param name="license"></param>
        /// <param name="driverName"></param>
        /// <param name="companyId"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public async Task<PageResponse<CarDto>> GetShowCar(string carBrand, string license, string driverName, int? companyId,int pageindex, int pagesize)
        {
            PageResponse<CarDto> response = new PageResponse<CarDto>();
            var sql = $"select a.*,b.CompanyName from Car a join Company b on a.CompanyId=b.Id where a.IsDelete=0  ";
            if(!string.IsNullOrWhiteSpace(carBrand))
            {
                sql += $" and a.CarBrand='{carBrand}'";
            }
            if (!string.IsNullOrWhiteSpace(license))
            {
                sql += $" and a.License='{license}'";
            }
            if (!string.IsNullOrWhiteSpace(driverName))
            {
                sql += $" and a.DriverName like '%" + driverName + "%'";
            }
            if (companyId>0)
            {
                sql += $" and a.CompanyId='{companyId}'";
            }
            var list = await this.QueryAsync<CarDto>(sql, null);
            response.PageTotal = list.Count;
            sql += $"order by a.Id offset {(pageindex - 1) * pagesize} rows fetch next {pagesize} rows only";
            var lists = await this.QueryAsync<CarDto>(sql, null);
            response.PageData = lists;
            return response;
        }
        /// <summary>
        /// 测试
        /// </summary>
        /// <returns></returns>
        public async Task<List<CarDto>> GetShowCars()
        {
            var sql = "select * from Car";
            return await this.QueryAsync<CarDto>(sql, null);
        }
        /// <summary>
        /// 显示公司
        /// </summary>
        /// <returns></returns>
        public async Task<List<CompanyDto>> GetShowCompany()
        {
            PageResponse<CompanyDto> response = new PageResponse<CompanyDto>();
            var sql = $"select * from Company where IsDelete=0  ";
            response.PageData = await this.QueryAsync<CompanyDto>(sql, null);
            return response.PageData;
        }
        /// <summary>
        /// 删除车辆
        /// </summary>
        /// <returns></returns>
        public async Task<int> DeleteDelCar(int id)
        {
            
            var sql = $"update Car set IsDelete=1 where Id={id}";
            return await ExecuteAsync(sql, null);
        }
        /// <summary>
        /// 批量删除车辆
        /// </summary>
        /// <returns></returns>
        public async Task<int> DeleteDelsCar(string ids)
        {
            var list = 0;
            var id=ids.Split(',');
            foreach (var item in id)
            {
                var sql = $"update Car set IsDelete=1 where Id={item}";
                list = await ExecuteAsync(sql, null);
            }
            return list;
        }
        /// <summary>
        /// 编辑修改
        /// </summary>
        /// <param name="carDto"></param>
        /// <returns></returns>
        public async Task<int> PutUpdateCar(CarDto carDto)
        {
            var sql = $"update Car set CarBrand='{carDto.CarBrand}',License='{carDto.License}',DriverName='{carDto.DriverName}',CompanyId='{carDto.CompanyId}',CarType='{carDto.CarType}',CarColor='{carDto.CarColor}',PurchaseTime='{carDto.PurchaseTime.ToString("yyyy/MM/dd")}',MarketingNumber='{carDto.MarketingNumber}',ExpiryTime='{carDto.ExpiryTime.ToString("yyyy/MM/dd")}',CheckTime='{carDto.CheckTime.ToString("yyyy/MM/dd")}',Kilometre='{carDto.Kilometre}' where Id='{carDto.Id}'";
            return await ExecuteAsync(sql, null);
        }
        /// <summary>
        /// 添加车辆
        /// </summary>
        /// <param name="carDto"></param>
        /// <returns></returns>
        public async Task<int> PostAddCar(CarDto carDto)
        {
            return await ExecuteAsync
                ($"insert into Car values('{carDto.CarNumber}','{carDto.CarBrand}','{carDto.License}','{carDto.DriverName}','{carDto.CompanyId}','{carDto.CarType}','{carDto.CarColor}','{carDto.PurchaseTime.ToString("yyyy/MM/dd")}','{carDto.MarketingNumber}','{carDto.ExpiryTime.ToString("yyyy/MM/dd")}','{carDto.CheckTime.ToString("yyyy/MM/dd")}','{carDto.Kilometre}','{carDto.CarImg}','{carDto.ExpiryImg}',0)",carDto);
        }
        public async Task<object> Addsss()
        {
            var sql= $"select CarNumber from Car where Id = 1";
            return await ExecuteScalarAsync(sql,null);
        }
    }
}
